package com.mapper;

import com.Dto.AllCounselorInfoDto;
import com.Dto.AllSupervisorInfoDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface AllCounselorInfoMapper {

    /**所有咨询师信息，包括咨询师姓名，咨询师的user_id, 咨询师绑定的督导姓名，咨询总次数，咨询总时长，本月排班日期
     *
     * @return
     */

    @Select("SELECT c.name, c.user_id AS counselorId, GROUP_CONCAT(DISTINCT s.name) AS supervisorBoundName,\n" +
            "       csession.totalSessionCount,\n" +
            "       SEC_TO_TIME(csession.totalSessionDurationSec) AS totalSessionDuration,\n" +
            "       GROUP_CONCAT(DISTINCT cs.date) AS date\n" +
            "FROM counselor c\n" +
            "         LEFT JOIN counselor_scheduling cs ON cs.counselor_id = c.id AND\n" +
            "                                              MONTH(cs.date) = MONTH(CURRENT_DATE()) AND YEAR(cs.date) = YEAR(CURRENT_DATE())\n" +
            "         LEFT JOIN relationship r ON c.id = r.counselor_id\n" +
            "         LEFT JOIN supervisor s ON r.supervisor_id = s.id\n" +
            "         LEFT JOIN (\n" +
            "    SELECT counselor_id, SUM(TIME_TO_SEC(session_duration)) AS totalSessionDurationSec,\n" +
            "           COUNT(counseling_session.id) AS totalSessionCount\n" +
            "    FROM counseling_session\n" +
            "    GROUP BY counselor_id\n" +
            ") csession ON csession.counselor_id = c.id\n" +
            "GROUP BY c.id, csession.totalSessionCount, totalSessionDuration;\n")
    List<AllCounselorInfoDto> findAllCounselorInfoDto();

}
